/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package keshe;
import java.awt.BorderLayout;
import java.awt.TextArea;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;

public class SubjectScoreRanking extends IndexOrManage implements ActionListener{

    String[] subjects={"","语文","英语","数学"};
    JComboBox subject=new JComboBox(subjects);
    JLabel la=new JLabel("选择查询科目");

    public SubjectScoreRanking(){
        super();
        p1.add(la);
        p1.add(subject);
        //注册监听
        b1.addActionListener(this);
        b2.addActionListener(this);
        b3.addActionListener(this);

        pack();
        setVisible(true);
    }
    @Override
    public void actionPerformed(ActionEvent e) {
        // TODO 自动生成的方法存根
        if(e.getActionCommand()=="显示"){
            text.setText("");
            String s="";
            if(subject.getSelectedItem().equals("语文"))
                s="Chinese";
            else if(subject.getSelectedItem().equals("英语"))
                s="English";
            else if(subject.getSelectedItem().equals("数学"))
                s="Maths";
            else
                JOptionPane.showMessageDialog(null, "找不到该科目", "Message",
                        JOptionPane.INFORMATION_MESSAGE);
            if(s.equals("")){

            }
            else
                try{
                    Connection con=DataBaseCreate.getConnection();
                    Statement sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                    ResultSet rs=sql.executeQuery("select "+s+" from student");
                    rs.last();
                    //获取数据总行数
                    int row=rs.getRow();
                    float score[]=new float[row];
                    rs.first();
                    int i=0;
                    //将指定科目成绩放入数组
                    while(i<row){
                        score[i]=rs.getFloat(s);
                        rs.next();
                        i+=1;
                    }
                    //快速排序
                    Arrays.sort(score);
                    text.setLineWrap(true);        //激活自动换行功能
                    text.setWrapStyleWord(true);   // 激活断行不断字功能

                    text.append(subject.getSelectedItem()+"成绩排名:\r\n");
                    text.append("排名        姓名        成绩        \r\n");
                    ResultSet rs1;
                    int rank=1;
                    float ave=0;   		//计算平均分
                    for(int j=row-1;j>=0;--j){
                        rs1=sql.executeQuery("select * from student where "+s+" like '%"+score[j]+"%'");
                        rs1.next();
                        text.append(rank+"           "+rs1.getString("Name")+"           "
                                +rs1.getFloat(s)+"\r\n");
                        rank+=1;
                        ave+=rs1.getFloat(s);
                    }
                    ave=ave/(rank-1);
                    text.append(subject.getSelectedItem()+"平均成绩: "+ave+"\r\n");

                }catch(java.lang.ClassNotFoundException err){
                    JOptionPane.showMessageDialog(null, err.getMessage(), "ClassNotFoundException:",
                            JOptionPane.INFORMATION_MESSAGE);
                }catch (SQLException ex){
                    JOptionPane.showMessageDialog(null, ex.getMessage(), "SQLException: ", JOptionPane.INFORMATION_MESSAGE);
                }
        }
        else if(e.getActionCommand()=="取消"){
            setVisible(false);
        }
        else if(e.getActionCommand()=="保存"){
            TxtSave save=new TxtSave(text.getText(),"按"+subject.getSelectedItem()+"成绩排名");
        }
    }

}

